package com.ruoyi.web.controller.spps.util;

import java.io.OutputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

@Component
public class ConnectionUtil {
	
	@Value("${spps.dbUrl}")
	private String url;
	public Connection getConnetion1() throws Exception {
		String driver = "oracle.jdbc.OracleDriver"; // 驱动标识符
		String user = "pps"; // 数据库的用户名
		String password = "pps"; // 数据库的密码
		Connection con = null;
		Class.forName(driver);
		con = DriverManager.getConnection(url, user, password);
		return con;
	}
	public Connection getConnetion() throws Exception {
		String driver = "oracle.jdbc.OracleDriver"; // 驱动标识符
		String user = "chenggevi"; // 数据库的用户名
		String password = "chenggevi"; // 数据库的密码
		Connection con = null;
		Class.forName(driver);
		con = DriverManager.getConnection(url, user, password);
		return con;
	}
	
	public String insertBlob(byte [] bytes,String id,String fileName ) throws Exception  {
			fileName = fileName.replace(".doc", ".pdf");
			String result = "上传成功";
			Connection con= null;
			Connection con1= null;
			Statement st= null;
			Statement st1= null;
			try {
				con = getConnetion();//chenggevi的数据库连接
				con1 = getConnetion1();//pps的数据库连接
				 con.setAutoCommit(false);   
				    st = con.createStatement(); 
				    st1 = con1.createStatement(); 
				    ResultSet rs1 = st1.executeQuery("select t.*  from T_CHK_BILL_DETAIL t  where t.id='"+id+"'");
				    if (rs1.next())   
				    {   
				    	String CITEM_NAME = rs1.getString("CITEM_NAME");
				    	String CITEM_CODE = rs1.getString("CITEM_CODE");
				    	String CHECK_MODEL = rs1.getString("CHECK_MODEL");
				    	String PROCESSER = rs1.getString("PROCESSER");
				    	Date PROCESS_DATE = rs1.getDate("PROCESS_DATE");
				    	String PRODUCT_NAME = rs1.getString("PRODUCT_NAME");
				    	String PRODU_UID = rs1.getString("PRODU_UID");
				    	String OP_NAME = rs1.getString("OP_NAME");
				    	String OP_CODE = rs1.getString("OP_CODE");
				    	String WORKSTATION_CODE = "GW033008009005";
				    	String ORDERCODE = rs1.getString("ORDERCODE");
				    	String CODE = rs1.getString("CODE");
				    	st1.clearBatch();
					    st1.close();
					    //________________________________________________________________________________________________
					    //插入一个空对象empty_blob()   
				    	ResultSet rsExist = st.executeQuery("select t.*, t.rowid from CGZX_CHKBILL_MES t  where t.Gid='"+id+"'");
				    	if(rsExist.next()){//有数据则更新
				    		 st.executeUpdate("update CGZX_CHKBILL_MES set FILE_CONTENT = empty_blob() where gid ='"+id+"'");   
							    //锁定数据行进行更新，注意“for update”语句   
							 ResultSet rs = st.executeQuery("select FILE_CONTENT  from CGZX_CHKBILL_MES t  where t.Gid='"+id+"' for update");   
							      
							    if (rs.next())   
							    {   
							    	
							        //得到java.sql.Blob对象后强制转换为oracle.sql.BLOB   
							        oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("FILE_CONTENT");   
							        @SuppressWarnings("deprecation")
									OutputStream outStream = blob.getBinaryOutputStream();   
							        
							        //data是传入的byte数组，定义：byte[] data   
							        outStream.write(bytes, 0, bytes.length);  
							        st.clearBatch();
							        st.close();
							        outStream.flush();   
								    outStream.close();
							    }   
							    
							    //在pps当中添加FILE_CONTENT ---------------------------------------------------------------------------
							    Statement st3 = con1.createStatement();
							    st3.executeUpdate("update T_CHK_BILL_DETAIL set FILE_CONTENT = empty_blob() where id ='"+id+"'");   
							    //锁定数据行进行更新，注意“for update”语句   
							    ResultSet rs2 = st3.executeQuery("select FILE_CONTENT  from T_CHK_BILL_DETAIL t  where t.id='"+id+"' for update");   
							      
							    if (rs2.next())   
							    {   
							    	
							        //得到java.sql.Blob对象后强制转换为oracle.sql.BLOB   
							        oracle.sql.BLOB blob = (oracle.sql.BLOB) rs2.getBlob("FILE_CONTENT");   
							        @SuppressWarnings("deprecation")
									OutputStream outStream = blob.getBinaryOutputStream();   
							        
							        //data是传入的byte数组，定义：byte[] data   
							        outStream.write(bytes, 0, bytes.length);  
							        st3.clearBatch();
							        st3.close();
							        outStream.flush();   
								    outStream.close();
							    }
							    //相当于添加了事物
							    con1.commit();
							    con.commit();
				    	}else{//判读CGZX_CHKBILL_MES是否又数据  没有数据则插入
				    		StringBuffer sb = new StringBuffer();
				    		sb.append("insert into CGZX_CHKBILL_MES (OP_CODE,REMARK,FLAG,GID,CITEM_NAME,CITEM_CODE,CHECK_MODEL,PROCESSER,PROCESS_DATE,PRODUCT_NAME,PRODU_UID,OP_NAME,WORKSTATION_CODE,DISPATCH_CODE,CODE,FILE_CONTENT)");
				    		sb.append(" values('"+OP_CODE+"','"+fileName+"',0,'"+id+"','"+CITEM_NAME+"','"+CITEM_CODE+"',4,'李海强',to_date(to_char(sysdate, 'yyyy-MM-dd'), 'yyyy-mm-dd'),'"+PRODUCT_NAME+"','"+PRODU_UID+"','"+OP_NAME+"','"+WORKSTATION_CODE+"','"+ORDERCODE+"','"+CODE+"',empty_blob())");
				    		st.executeUpdate(sb.toString());   
						    //锁定数据行进行更新，注意“for update”语句   
				    		ResultSet rs = st.executeQuery("select FILE_CONTENT  from CGZX_CHKBILL_MES t  where t.gid='"+id+"' for update");   
						      
						    if (rs.next())   
						    {   
						    	
						        //得到java.sql.Blob对象后强制转换为oracle.sql.BLOB   
						        oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("FILE_CONTENT");   
						        @SuppressWarnings("deprecation")
								OutputStream outStream = blob.getBinaryOutputStream();   
						        
						        //data是传入的byte数组，定义：byte[] data   
						        outStream.write(bytes, 0, bytes.length);   
						        outStream.flush();   
							    outStream.close();
						    }   
						    
						    //在pps当中添加FILE_CONTENT ---------------------------------------------------------------------------
						    Statement st3 = con1.createStatement();
						    st3.executeUpdate("update T_CHK_BILL_DETAIL set FILE_CONTENT = empty_blob() where id ='"+id+"'");   
						    //锁定数据行进行更新，注意“for update”语句   
						    ResultSet rs2 = st3.executeQuery("select FILE_CONTENT  from T_CHK_BILL_DETAIL t  where t.id='"+id+"' for update");   
						      
						    if (rs2.next())   
						    {   
						    	
						        //得到java.sql.Blob对象后强制转换为oracle.sql.BLOB   
						        oracle.sql.BLOB blob = (oracle.sql.BLOB) rs2.getBlob("FILE_CONTENT");   
						        @SuppressWarnings("deprecation")
								OutputStream outStream = blob.getBinaryOutputStream();   
						        
						        //data是传入的byte数组，定义：byte[] data   
						        outStream.write(bytes, 0, bytes.length);  
						        st3.clearBatch();
						        st3.close();
						        outStream.flush();   
							    outStream.close();
						    }
						    //相当于添加了事物控制
						    con.commit(); 
						    con1.commit();
				    	}
					   
				    }
				   
			} catch (Exception e) {
				// TODO Auto-generated catch block
				result = "上传失败" ;
				con.rollback();
				con1.rollback();
				e.printStackTrace();
			}finally{
				con.close();
				con1.close();
			}
			return result ;
			  
	}
	public String fileuoloadState(String id) throws SQLException {
		String result = null ;
		Statement st= null;
		Connection con = null ;
		try {
			 con= getConnetion();
			 st= con.createStatement();
			ResultSet rsExist = st.executeQuery("select t.* from CGZX_CHKBILL_MES t  where t.Gid='"+id+"'");
			if(rsExist.next()){
				result = "已上传" ;
			}else{
				result ="未上传" ;
			}
			st.clearBatch();
			st.close() ;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.close() ;
		}
		
		return result ;
	}
}
